﻿using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
using WPFExcelReport.Helpers;
using WPFExcelReport.Controls;
namespace WPFExcelReport
{
    public partial class VQPMERKM
    {
        public void SetUpNewRow(ref DataRow dwReturn)
        {

            switch (app.company)
            {
                case "YGR":
                case "YCC":
                case "TST":
                default:
                    #region=========
                    dwReturn["QPMMANDANT"] = app.Mandant;
                    dwReturn["QPMMERKM"] = "    ";
                    dwReturn["QPMAENZL"] = "0";
                    dwReturn["QPMAENB"] = "        ";
                    dwReturn["QPMAEND"] = "0";
                    dwReturn["QPMNEUB"] = "        ";
                    dwReturn["QPMNEUD"] = "0";
                    dwReturn["QPMKURZB"] = "                    ";
                    dwReturn["QPMBEZ"] = "                                                  ";
                    dwReturn["QPMNORM"] = "               ";
                    dwReturn["QPMVERS"] = "00";
                    dwReturn["QPMWERTV"] = "0";
                    dwReturn["QPMWERTB"] = "0";
                    dwReturn["QPMMEEH"] = "   ";
                    dwReturn["QPMSDDAT"] = "    ";
                    dwReturn["QPMNUMKZ"] = "J";
                    dwReturn["QPMWBER"] = "          ";
                    dwReturn["QPMKZ"] = " ";
                    dwReturn["QPMPREISR"] = " ";
                    dwReturn["QPMVARDBL1"] = "  ";
                    dwReturn["QPMVARDBL2"] = "  ";
                    dwReturn["QPMVARDBL3"] = "  ";
                    dwReturn["QPMVARDBL4"] = "  ";
                    dwReturn["QPMVARDBL5"] = "  ";
                    dwReturn["QPMVARPRZG1"] = "  ";
                    dwReturn["QPMVARPRZG2"] = "  ";
                    dwReturn["QPMVARPRZG3"] = "  ";
                    dwReturn["QPMVARPRZG4"] = "  ";
                    dwReturn["QPMVARPRZG5"] = "  ";
                    dwReturn["QPMSAMHIST"] = " ";
                    dwReturn["QPMAWERTV"] = "          ";
                    dwReturn["QPMAWERTB"] = "          ";
                    dwReturn["QPMANZNK"] = "0";
                    dwReturn["QPMWAUFB"] = " ";
                    dwReturn["QPMROUND"] = " ";
                    dwReturn["QPMFEATURE"] = "0";
                    dwReturn["QPMLOGO"] = "  ";
                    dwReturn["QPMGPFELD"] = "            ";
                    dwReturn["QPMFORMEL"] = "  ";
                    dwReturn["QPMFORMELV"] = "  ";
                    dwReturn["QPMFILLER"] = "                              ";
                    #endregion
                    break;
            }
        }

        public void InsertQPMERKM(DataRow dwData, string ITXDBVERSION = "50.24")
        {
             or1 = OracleDB.CreateCommand();
            switch (ITXDBVERSION)
            {
                default:
                case "JLT":
                    or1.CommandText = "INSERT INTO QPMERKM ( QPMMANDANT,QPMMERKM,QPMAENZL,QPMAENB,QPMAEND,QPMNEUB,QPMNEUD,QPMKURZB,QPMBEZ,QPMNORM,QPMVERS,QPMWERTV,QPMWERTB,QPMMEEH,QPMSDDAT,QPMNUMKZ,QPMWBER,QPMKZ,QPMPREISR,QPMVARDBL1,QPMVARDBL2,QPMVARDBL3,QPMVARDBL4,QPMVARDBL5,QPMVARPRZG1,QPMVARPRZG2,QPMVARPRZG3,QPMVARPRZG4,QPMVARPRZG5,QPMSAMHIST,QPMAWERTV,QPMAWERTB,QPMANZNK,QPMWAUFB,QPMROUND,QPMFEATURE,QPMLOGO,QPMGPFELD,QPMFORMEL,QPMFORMELV,QPMFILLER)"
                    + " VALUES (:QPMMANDANT:,:QPMMERKM:,:QPMAENZL:,:QPMAENB:,:QPMAEND:,:QPMNEUB:,:QPMNEUD:,:QPMKURZB:,:QPMBEZ:,:QPMNORM:,:QPMVERS:,:QPMWERTV:,:QPMWERTB:,:QPMMEEH:,:QPMSDDAT:,:QPMNUMKZ:,:QPMWBER:,:QPMKZ:,:QPMPREISR:,:QPMVARDBL1:,:QPMVARDBL2:,:QPMVARDBL3:,:QPMVARDBL4:,:QPMVARDBL5:,:QPMVARPRZG1:,:QPMVARPRZG2:,:QPMVARPRZG3:,:QPMVARPRZG4:,:QPMVARPRZG5:,:QPMSAMHIST:,:QPMAWERTV:,:QPMAWERTB:,:QPMANZNK:,:QPMWAUFB:,:QPMROUND:,:QPMFEATURE:,:QPMLOGO:,:QPMGPFELD:,:QPMFORMEL:,:QPMFORMELV:,:QPMFILLER:)";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData); 
                    break;
                case "YGR":
                    or1.CommandText = "INSERT INTO QPMERKM ( QPMMANDANT,QPMMERKM,QPMAENZL,QPMAENB,QPMAEND,QPMNEUB,QPMNEUD,QPMKURZB,QPMBEZ,QPMNORM,QPMVERS,QPMWERTV,QPMWERTB,QPMMEEH,QPMSDDAT,QPMNUMKZ,QPMWBER,QPMKZ,QPMPREISR,QPMVARDBL1,QPMVARDBL2,QPMVARDBL3,QPMVARDBL4,QPMVARDBL5,QPMVARPRZG1,QPMVARPRZG2,QPMVARPRZG3,QPMVARPRZG4,QPMVARPRZG5,QPMSAMHIST,QPMAWERTV,QPMAWERTB,QPMANZNK,QPMWAUFB,QPMROUND,QPMFEATURE,QPMLOGO,QPMGPFELD,QPMFORMEL,QPMFORMELV,QPMFILLER)"
                    + " VALUES (:QPMMANDANT:,:QPMMERKM:,:QPMAENZL:,:QPMAENB:,:QPMAEND:,:QPMNEUB:,:QPMNEUD:,:QPMKURZB:,:QPMBEZ:,:QPMNORM:,:QPMVERS:,:QPMWERTV:,:QPMWERTB:,:QPMMEEH:,:QPMSDDAT:,:QPMNUMKZ:,:QPMWBER:,:QPMKZ:,:QPMPREISR:,:QPMVARDBL1:,:QPMVARDBL2:,:QPMVARDBL3:,:QPMVARDBL4:,:QPMVARDBL5:,:QPMVARPRZG1:,:QPMVARPRZG2:,:QPMVARPRZG3:,:QPMVARPRZG4:,:QPMVARPRZG5:,:QPMSAMHIST:,:QPMAWERTV:,:QPMAWERTB:,:QPMANZNK:,:QPMWAUFB:,:QPMROUND:,:QPMFEATURE:,:QPMLOGO:,:QPMGPFELD:,:QPMFORMEL:,:QPMFORMELV:,:QPMFILLER:)";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break; 
            } 
            Connect2Proxy(or1);
        }
        public DataSet QueryQPMERKM(DataRow dwData, int option = 1)
        { 
            
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case  1:
                    or1.CommandText = "SELECT *FROM QPMERKM ";
                    break;
                case 2:
                    or1.CommandText = "SELECT *FROM QPMERKM WHERE QPMMANDANT =:QPMMANDANT: AND QPMMERKM LIKE '" 
                        + dwData["QPMMERKM"].ToString().Trim() 
                        + "%' AND QPMBEZ LIKE '"+ dwData["QPMBEZ"].ToString().Trim()
                        + "%' AND QPMNORM LIKE '" + dwData["QPMNORM"].ToString().Trim() + "%'"
                        + " ORDER BY QPMNORM,QPMMERKM "
                        ;
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData); 
                    break;
                case 3:
                    or1.CommandText = "SELECT *FROM QPMERKM WHERE  QPMMANDANT =:QPMMANDANT: AND  QPMMERKM =:QPMMERKM:";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData); 
                    break; 
                case -1:
                    or1.CommandText = "SELECT QPMMERKM,QPMMERKM AS KEY,QPMKURZB,QPMKURZB AS DES,QPMVERS,QPMWERTV,QPMWERTB,QPMMEEH FROM QPMERKM WHERE QPMMANDANT=:QPMMANDANT:";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData); 
                    break;
                case -2:
                    or1.CommandText = "SELECT DISTINCT QPMMERKM FROM QPMERKM WHERE QPMMANDANT =:QPMMANDANT:"
                        + " ORDER BY 1";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                case -3:
                    or1.CommandText = "SELECT DISTINCT QPMMERKM FROM QPMERKM WHERE QPMMANDANT =:QPMMANDANT: AND (QPMMERKM LIKE 'B%' OR QPMMERKM  LIKE 'X%')"
                        + " ORDER BY 1";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
            } 
            SResult = Connect2Proxy(or1);
            SResult.Tables[0] .TableName = TableName;  
            return SResult;
        }
        public void DeleteQPMERKM(DataRow dwData, int option = 1)
        {

            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "DELETE FROM QPMERKM   WHERE  QPMMANDANT=:QPMMANDANT: AND QPMMERKM=:QPMMERKM:  ";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData); 
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
        }

        public void UpdateQPMERKM(DataRow dwData, string ITXDBVERSION = "50.24", int option = 1)
        {  
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 0://JLT
                    or1.CommandText = "UPDATE QPMERKM SET QPMMANDANT=:QPMMANDANT:,QPMMERKM=:QPMMERKM:,QPMAENZL=:QPMAENZL:,QPMAENB=:QPMAENB:,QPMAEND=:QPMAEND:,QPMNEUB=:QPMNEUB:,QPMNEUD=:QPMNEUD:,QPMKURZB=:QPMKURZB:,QPMBEZ=:QPMBEZ:,QPMNORM=:QPMNORM:,QPMVERS=:QPMVERS:,QPMWERTV=:QPMWERTV:,QPMWERTB=:QPMWERTB:,QPMMEEH=:QPMMEEH:,QPMSDDAT=:QPMSDDAT:,QPMNUMKZ=:QPMNUMKZ:,QPMWBER=:QPMWBER:,QPMKZ=:QPMKZ:,QPMPREISR=:QPMPREISR:,QPMVARDBL1=:QPMVARDBL1:,QPMVARDBL2=:QPMVARDBL2:,QPMVARDBL3=:QPMVARDBL3:,QPMVARDBL4=:QPMVARDBL4:,QPMVARDBL5=:QPMVARDBL5:,QPMVARPRZG1=:QPMVARPRZG1:,QPMVARPRZG2=:QPMVARPRZG2:,QPMVARPRZG3=:QPMVARPRZG3:,QPMVARPRZG4=:QPMVARPRZG4:,QPMVARPRZG5=:QPMVARPRZG5:,QPMSAMHIST=:QPMSAMHIST:,QPMAWERTV=:QPMAWERTV:,QPMAWERTB=:QPMAWERTB:,QPMANZNK=:QPMANZNK:,QPMWAUFB=:QPMWAUFB:,QPMROUND=:QPMROUND:,QPMFEATURE=:QPMFEATURE:,QPMLOGO=:QPMLOGO:,QPMGPFELD=:QPMGPFELD:,QPMFORMEL=:QPMFORMEL:,QPMFORMELV=:QPMFORMELV:,QPMFILLER=:QPMFILLER:"
                        + " WHERE QPMMANDANT=:QPMMANDANT: AND QPMMERKM=:QPMMERKM:";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                default:
                    break;
            }

            Connect2Proxy(or1);
        }
    }
}
